﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using OpenSSL.X509;
using System.Data.OleDb;
using System.Configuration;
using System.Web;
using System.IO;
using OpenSSL.Crypto;

namespace Sicurezza.CA.DataProvider
{
    public class SSLDataProvider
    {
       

        public int Save(string ReqId, int ProfileId, X509Request r, DateTime notBefore, DateTime notAfter)
        {
            int serial = 0;

            using (OleDbConnection conn = new OleDbConnection(ConnectionStringDataProvider.ConnectionString))
            {
                conn.Open();

                   string query = "INSERT INTO Certificates " +
                                   "(RequestNr, NotBefore,NotAfter,DigitalSignature,KeyEncipherment,ProfileId) " +
                                   "VALUES ('" + ReqId + "'," + getInsertValues(r, notBefore, notAfter) + ","+ProfileId+")";

                    OleDbCommand cmd = new OleDbCommand(query, conn);

                    cmd.ExecuteNonQuery();

                    cmd.CommandText = "SELECT @@Identity";
                    serial = Convert.ToInt32(cmd.ExecuteScalar());

            }

            return serial;

        }

        public bool Exist(X509Name subject, int ProfileId)
        {
            bool exist = false;
            using (OleDbConnection conn = new OleDbConnection(ConnectionStringDataProvider.ConnectionString))
            {
                conn.Open();
                //-- Controllo se esiste già uno stesso certificato al momento valido
                //-- e con le caratteristiche richieste. Se esiste, rifiuto la richiesta.

                string query = "SELECT COUNT(*) " +
                               "FROM Certificates c " +
                               "WHERE " + getCheckValues(subject, ProfileId);

                OleDbCommand cmd = new OleDbCommand(query, conn);
                exist = Convert.ToInt32(cmd.ExecuteScalar()) > 0;
            }
            return exist;
        }

        private string getCheckValues(X509Name subject, int ProfileId)
        {
            StringBuilder sb = new StringBuilder();
            bool digitalSignature = false;
            bool keyEncipherment = false;
            string keyUsage = subject.GetTextByName("keyUsage");
            if (keyUsage != null)
            {
                digitalSignature = keyUsage.Contains("digitalSignature");
                keyEncipherment = keyUsage.Contains("keyEncipherment");
            }


            sb.Append("#" + DateTime.Now.ToString("yyyy-MM-dd hh:mm:ss") + "#");
            sb.Append(" BETWEEN notBefore AND notAfter ");
            sb.Append(" AND ");
            sb.Append("DigitalSignature=" + digitalSignature.ToString());
            sb.Append(" AND ");
            sb.Append("KeyEncipherment=" + keyEncipherment.ToString());
            sb.Append(" AND ");
            sb.Append("ProfileId=" + ProfileId);

            return sb.ToString();
        }

        private string getInsertValues(X509Request r, DateTime notBefore, DateTime notAfter)
        {
            // YYYY-MM-DD HH:MM:DD
            StringBuilder sb = new StringBuilder();

bool digitalSignature =false;
bool keyEncipherment = false;
            string keyUsage = r.Subject.GetTextByName("keyUsage");
            if (keyUsage != null)
            {
                digitalSignature = keyUsage.Contains("digitalSignature");
                keyEncipherment = keyUsage.Contains("keyEncipherment");
            }

            sb.Append("#" + notBefore.ToString("yyyy-MM-dd hh:mm:ss") + "#");
            sb.Append(",");
            sb.Append("#" + notAfter.ToString("yyyy-MM-dd hh:mm:ss") + "#");
            sb.Append(",");
            sb.Append(digitalSignature.ToString());
            sb.Append(",");
            sb.Append(keyEncipherment.ToString());

            return sb.ToString();
        }


        

        private string getInsertValues(X509Request r, DateTime dateTime)
        {
            // YYYY-MM-DD HH:MM:DD
            StringBuilder sb = new StringBuilder();

            string keyUsage = r.Subject.GetTextByName("keyUsage");
            bool digitalSignature = keyUsage.Contains("digitalSignature");
            bool keyEncipherment = keyUsage.Contains("keyEncipherment");

            sb.Append("#" + dateTime.ToString("yyyy-MM-dd hh:mm:ss") + "#");
            sb.Append(",");
            sb.Append("'" + r.Subject.Country + "'");
            sb.Append(",");
            sb.Append("'" + r.Subject.Organization + "'");
            sb.Append(",");
            sb.Append("'" + r.Subject.OrganizationUnit + "'");
            sb.Append(",");
            sb.Append("'" + r.Subject.Common + "'");
            sb.Append(",");
            sb.Append("'" + r.Subject.GetTextByName("subjectAltName").Substring(6) + "'");
            sb.Append(",");
            sb.Append(digitalSignature.ToString());
            sb.Append(",");
            sb.Append(keyEncipherment.ToString());

            return sb.ToString();
        }

        public int GetCertificate(string ReqId)
        {
            // TODO: da sistemare con il nuovo pattern di richiesta formato stringa
            int serial = 0;
            using (OleDbConnection conn = new OleDbConnection(ConnectionStringDataProvider.ConnectionString))
            {
                string query = "SELECT Id " +
                               "FROM Certificates " +
                               "WHERE RequestNr='" + ReqId+"'";

                OleDbCommand cmd = new OleDbCommand(query, conn);
                conn.Open();
                serial = Convert.ToInt32(cmd.ExecuteScalar());
            }

            return serial;
        }

        public int SaveRevokeRequest(X509Certificate cert)
        {
            int serial = 0;
            using (OleDbConnection conn = new OleDbConnection(ConnectionStringDataProvider.ConnectionString))
            {
                string query = "INSERT INTO Revokes " +
                               "([Timestamp],C,O,OU,CN) " +
                               "VALUES (" + getInsertValues(cert.CreateRequest(cert.PublicKey, MessageDigest.Null) ,DateTime.Now) + ")";

                OleDbCommand cmd = new OleDbCommand(query, conn);
                conn.Open();
                cmd.ExecuteNonQuery();

                cmd.CommandText = "SELECT @@Identity";
                serial = Convert.ToInt32(cmd.ExecuteScalar());
            }

            return serial;
        }

        public List<int> GetAll(bool signature, bool encipher)
        {
            List<int> serials = new List<int>();
            using (OleDbConnection conn = new OleDbConnection(ConnectionStringDataProvider.ConnectionString))
            {
                string query = "SELECT c.id as seriale " +
                               "FROM Certificates c LEFT JOIN Revokes r ON c.id = r.serial " +
                               "WHERE r.id IS NULL "+
                               "AND #" + DateTime.Now.ToString("yyyy-MM-dd hh:mm:ss") + "# BETWEEN notBefore AND notAfter ";
                if (!(signature && encipher))
                {
                    if (signature)
                        query += "AND DigitalSignature=true ";

                    if (encipher)
                        query += " AND KeyEncipherment=true ";
                }
                OleDbCommand cmd = new OleDbCommand(query, conn);
                conn.Open();
                OleDbDataReader dr = cmd.ExecuteReader();

                while (dr.Read())
                {
                    serials.Add(Convert.ToInt32(dr["seriale"]));
                }
            }

            return serials;
        }
    }
}
